package com.huabo.contract.oracle.mapper;

import com.hbfk.util.DateUtil;
import com.hbfk.util.PageInfo;
import com.huabo.contract.oracle.entity.TblContractInvoicesmanagemen;

public class TblContractInvoicesmanagemenMapperSqlConfig {


    public String findInvoiceInfoListForCollection(PageInfo<TblContractInvoicesmanagemen> pageInfo, TblContractInvoicesmanagemen invoice) {
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT INVOICEID,INVOICENO,INVOICEMONEY,INVOICETYPE,INVOICEDATE,INVOICESTATUS,INVOICEKPORG,INVOICEHEADTEXT  FROM TBL_CONTRACT_INVOICESMANAGEMEN WHERE INVOICEOGR = " + invoice.getInvoiceogr() + " AND INVOICESTATUS != 5 AND INVOICEID NOT IN (SELECT INVOICEID FROM TBL_CONTRACT_COLLECTION WHERE LINKORG = " + invoice.getInvoiceogr() + " AND INVOICEID IS NOT NULL UNION ALL SELECT INVOICEID FROM TBL_CONTRACT_PAYMENT WHERE LINKORG = "+invoice.getInvoiceogr()+" AND INVOICEID IS NOT NULL)");
        if (invoice.getInvoiceno() != null && !"".equals(invoice.getInvoiceno())) {
            sbSql.append(" AND INVOICENO LIKE '%" + invoice.getInvoiceno() + "%'");
        }
        if (invoice.getInvoiceheadtext() != null && !"".equals(invoice.getInvoiceheadtext())) {
            sbSql.append(" AND INVOICEHEADTEXT LIKE '%" + invoice.getInvoiceheadtext() + "%'");
        }
        if (invoice.getInvoicekporg() != null && !"".equals(invoice.getInvoicekporg())) {
            sbSql.append(" AND INVOICEKPORG LIKE '%" + invoice.getInvoicekporg() + "%'");
        }
        if (invoice.getInvoicetype() != null && !"".equals(invoice.getInvoicetype())) {
            sbSql.append(" AND INVOICETYPE = '" + invoice.getInvoicetype() + "'");
        }
        if (invoice.getBudgetId() != null) {
            sbSql.append(" AND BUDGETID = " + invoice.getBudgetId());
        }

        sbSql.append(" ORDER BY INVOICEID DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }


    public String findInvoiceInfoListForCollectionCount(TblContractInvoicesmanagemen invoice) {
        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_CONTRACT_INVOICESMANAGEMEN WHERE INVOICEOGR = " + invoice.getOrgid() + " AND INVOICESTATUS != 5 AND INVOICEID NOT IN (SELECT INVOICEID FROM TBL_CONTRACT_COLLECTION WHERE LINKORG = " + invoice.getInvoiceogr()  + " AND INVOICEID IS NOT NULL UNION ALL SELECT INVOICEID FROM TBL_CONTRACT_PAYMENT WHERE LINKORG = "+invoice.getInvoiceogr()+" AND INVOICEID IS NOT NULL)");
        if (invoice.getInvoiceno() != null && !"".equals(invoice.getInvoiceno())) {
            sbSql.append(" AND INVOICENO LIKE '%" + invoice.getInvoiceno() + "%'");
        }
        if (invoice.getInvoiceheadtext() != null && !"".equals(invoice.getInvoiceheadtext())) {
            sbSql.append(" AND INVOICEHEADTEXT LIKE '%" + invoice.getInvoiceheadtext() + "%'");
        }
        if (invoice.getInvoicekporg() != null && !"".equals(invoice.getInvoicekporg())) {
            sbSql.append(" AND INVOICEKPORG LIKE '%" + invoice.getInvoicekporg() + "%'");
        }
        if (invoice.getInvoicetype() != null && !"".equals(invoice.getInvoicetype())) {
            sbSql.append(" AND INVOICETYPE = '" + invoice.getInvoicetype() + "'");
        }
        if (invoice.getBudgetId() != null) {
            sbSql.append(" AND BUDGETID = " + invoice.getBudgetId());
        }
       // sbSql.append(" ORDER BY INVOICEID DESC)");
        String sql = sbSql.toString();
        return sql;
    }


    public String findContractInvociesManaeMenPageInfo(PageInfo<TblContractInvoicesmanagemen> pageInfo, TblContractInvoicesmanagemen invoice) {
        StringBuffer sbSql = new StringBuffer("SELECT * FROM ( SELECT BUDGET.*,ROWNUM RNUM FROM (SELECT tcp.BUDGETNAME budgetName, ts.REALNAME realname, tci.*  FROM TBL_CONTRACT_INVOICESMANAGEMEN tci\n" +
                "left join TBL_STAFF ts on tci.CREATESTAFF = ts.STAFFID LEFT JOIN TBL_CYHW_PROJECTBUDGET tcp ON tci.BUDGETID = tcp.BUDGETID \n" +
                "WHERE INVOICEOGR = " + invoice.getInvoiceogr());
        if (invoice.getCreatestaff() != null) {
            sbSql.append(" AND CREATESTAFF = " + invoice.getCreatestaff());
        }

        if (invoice.getInvoiceno() != null && !"".equals(invoice.getInvoiceno())) {
            sbSql.append(" AND INVOICENO LIKE '%" + invoice.getInvoiceno() + "%'");
        }

        if (invoice.getInvoiceheadtext() != null && !"".equals(invoice.getInvoiceheadtext() )) {
            sbSql.append(" AND INVOICEHEADTEXT LIKE '%" + invoice.getInvoiceheadtext()  + "%'");
        }
        if (invoice.getInvoicecontent() != null &&  !"".equals(invoice.getInvoicecontent() )){
            sbSql.append(" AND INVOICECONTENT LIKE '%" + invoice.getInvoicecontent()  + "%'");
        }

        if (invoice.getInvoicetype() != null && !"".equals(invoice.getInvoicetype())) {
            sbSql.append(" AND INVOICETYPE = '" + invoice.getInvoicetype() + "'");
        }

        if (invoice.getInvoicestatus() != null) {
            sbSql.append(" AND INVOICESTATUS = " + invoice.getInvoicestatus());
        }

        sbSql.append(" ORDER BY INVOICEID DESC) BUDGET WHERE rownum <= "+(pageInfo.getCurrentPage()*pageInfo.getPageSize())+" ) WHERE RNUM > "+pageInfo.getCurrentRecord());
        String sql = sbSql.toString();
        return sql;
    }

    public String findContractInvociesManaeMenPageInfoCount(TblContractInvoicesmanagemen invoice) {
        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_CONTRACT_INVOICESMANAGEMEN tci\n" +
                "left join TBL_STAFF ts on tci.CREATESTAFF = ts.STAFFID\n" +
                "WHERE INVOICEOGR = " + invoice.getInvoiceogr());
        if (invoice.getCreatestaff() != null) {
            sbSql.append(" AND CREATESTAFF = " + invoice.getCreatestaff());
        }

        if (invoice.getInvoiceno() != null && !"".equals(invoice.getInvoiceno())) {
            sbSql.append(" AND INVOICENO = '%" + invoice.getInvoiceno() + "%'");
        }

        if (invoice.getInvoiceheadtext() != null && !"".equals(invoice.getInvoiceheadtext() )) {
            sbSql.append(" AND INVOICEHEADTEXT LIKE '%" + invoice.getInvoiceheadtext()  + "%'");
        }

        if (invoice.getInvoicetype() != null && !"".equals(invoice.getInvoicetype())) {
            sbSql.append(" AND INVOICETYPE = '" + invoice.getInvoicetype() + "'");
        }

        if (invoice.getInvoicestatus() != null) {
            sbSql.append(" AND INVOICESTATUS = " + invoice.getInvoicestatus());
        }

        sbSql.append(" ORDER BY INVOICEID DESC");
        String sql = sbSql.toString();
        return sql;
    }


    public String selectListCount(TblContractInvoicesmanagemen oldInvoice) {
        StringBuffer sbSql = new StringBuffer("SELECT COUNT(*) FROM TBL_CONTRACT_INVOICESMANAGEMEN WHERE INVOICEOGR = " + oldInvoice.getInvoiceogr() + " AND INVOICENO = '" + oldInvoice.getInvoiceno() + "'");
        if (oldInvoice.getInvoiceid() != null) {
            sbSql.append(" AND INVOICEID != " + oldInvoice.getInvoiceid());
        }
        String sql = sbSql.toString();
        return sql;
    }


    public String updateMergenEntity(TblContractInvoicesmanagemen oldInvoice) {
        StringBuffer sql = new StringBuffer("UPDATE TBL_CONTRACT_INVOICESMANAGEMEN SET INVOICENO = "+oldInvoice.getInvoiceno()+" ");

        if(oldInvoice.getBudgetId() != null && !"".equals(oldInvoice.getBudgetId())) {
            sql.append(" , BUDGETID = '"+oldInvoice.getBudgetId()+"'");
        }
        if(oldInvoice.getInvoicemoney() != null && !"".equals(oldInvoice.getInvoicemoney())) {
            sql.append(" , INVOICEMONEY = '"+oldInvoice.getInvoicemoney()+"'");
        }
        if(oldInvoice.getInvoiceheadtext() != null && !"".equals(oldInvoice.getInvoiceheadtext())) {
            sql.append(" , INVOICEHEADTEXT = '"+oldInvoice.getInvoiceheadtext()+"'");
        }
        if(oldInvoice.getInvoicepost() != null && !"".equals(oldInvoice.getInvoicepost())) {
            sql.append(" , INVOICEPOST = '"+oldInvoice.getInvoicepost()+"'");
        }
        if(oldInvoice.getInvoicecontent() != null && !"".equals(oldInvoice.getInvoicecontent())) {
            sql.append(" , INVOICECONTENT = '"+oldInvoice.getInvoicecontent()+"'");
        }
        if(oldInvoice.getInvoicestatus() != null && !"".equals(oldInvoice.getInvoicestatus())) {
            sql.append(" , INVOICESTATUS = '"+oldInvoice.getInvoicestatus()+"'");
        }
        if(oldInvoice.getInvoicespdate() != null && !"".equals(oldInvoice.getInvoicespdate())) {
            sql.append(" , INVOICESPDATE = TO_DATE('"+ DateUtil.parseDate(oldInvoice.getInvoicedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(oldInvoice.getInvoicesporg() != null && !"".equals(oldInvoice.getInvoicesporg())) {
            sql.append(" , INVOICESPORG = '"+oldInvoice.getInvoicesporg()+"'");
        }
        if(oldInvoice.getInvoicekporg() != null && !"".equals(oldInvoice.getInvoicekporg())) {
            sql.append(" , INVOICEKPORG = '"+oldInvoice.getInvoicekporg()+"'");
        }
        if(oldInvoice.getInvoiceogr() != null && !"".equals(oldInvoice.getInvoiceogr())) {
            sql.append(" , INVOICEOGR = '"+oldInvoice.getInvoiceogr()+"'");
        }
        if(oldInvoice.getCreatestaff() != null && !"".equals(oldInvoice.getCreatestaff())) {
            sql.append(" , CREATESTAFF = '"+oldInvoice.getCreatestaff()+"'");
        }
        if(oldInvoice.getTinumber() != null && !"".equals(oldInvoice.getTinumber())) {
            sql.append(" , TINUMBER = '"+oldInvoice.getTinumber()+"'");
        }
        sql.append(" WHERE INVOICEID = '"+oldInvoice.getInvoiceid()+"'");
        return sql.toString();
    }


    public String saveMergenEntity(TblContractInvoicesmanagemen oldInvoice) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_CONTRACT_INVOICESMANAGEMEN (INVOICEID");
        StringBuffer value = new StringBuffer(" VALUES (CIRCULATION_SEQUENCE.nextval");

        if(oldInvoice.getBudgetId()!= null && !"".equals(oldInvoice.getBudgetId())) {
            column.append(",BUDGETID");
            value.append(",'"+oldInvoice.getBudgetId()+"'");
        }
        if(oldInvoice.getInvoiceno()!= null && !"".equals(oldInvoice.getInvoiceno())) {
            column.append(",INVOICENO");
            value.append(",'"+oldInvoice.getInvoiceno()+"'");
        }
        if(oldInvoice.getInvoicemoney() != null && !"".equals(oldInvoice.getInvoicemoney())) {
            column.append(",INVOICEMONEY");
            value.append(",'"+oldInvoice.getInvoicemoney()+"'");
        }
        if(oldInvoice.getInvoicedate() != null && !"".equals(oldInvoice.getInvoicedate())) {
            column.append(",INVOICEDATE");
            value.append(",TO_DATE('"+ DateUtil.parseDate(oldInvoice.getInvoicedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(oldInvoice.getInvoiceheadtext() != null && !"".equals(oldInvoice.getInvoiceheadtext())) {
            column.append(",INVOICEHEADTEXT");
            value.append(",'"+oldInvoice.getInvoiceheadtext()+"'");
        }
        if(oldInvoice.getInvoicepost() != null && !"".equals(oldInvoice.getInvoicepost())) {
            column.append(",INVOICEPOST");
            value.append(",'"+oldInvoice.getInvoicepost()+"'");
        }
        if(oldInvoice.getInvoicecontent() != null && !"".equals(oldInvoice.getInvoicecontent())) {
            column.append(",INVOICECONTENT");
            value.append(",'"+oldInvoice.getInvoicecontent()+"'");
        }
        if(oldInvoice.getInvoicetype() != null && !"".equals(oldInvoice.getInvoicetype())) {
            column.append(",INVOICETYPE");
            value.append(",'"+oldInvoice.getInvoicetype()+"'");
        }
        if(oldInvoice.getInvoicestatus() != null && !"".equals(oldInvoice.getInvoicestatus())) {
            column.append(",INVOICESTATUS");
            value.append(",'"+oldInvoice.getInvoicestatus()+"'");
        }
        if(oldInvoice.getInvoicespdate() != null && !"".equals(oldInvoice.getInvoicespdate())) {
            column.append(",INVOICESPDATE");
            value.append(",TO_DATE('"+ DateUtil.parseDate(oldInvoice.getInvoicespdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");        }
        if(oldInvoice.getInvoicesporg() != null && !"".equals(oldInvoice.getInvoicesporg())) {
            column.append(",INVOICESPORG");
            value.append(",'"+oldInvoice.getInvoicesporg()+"'");
        }
        if(oldInvoice.getInvoicekporg() != null && !"".equals(oldInvoice.getInvoicekporg())) {
            column.append(",INVOICEKPORG");
            value.append(",'"+oldInvoice.getInvoicekporg()+"'");
        }
        if(oldInvoice.getInvoiceogr() != null && !"".equals(oldInvoice.getInvoiceogr())) {
            column.append(",INVOICEOGR");
            value.append(",'"+oldInvoice.getInvoiceogr()+"'");
        }
        if(oldInvoice.getCreatestaff() != null && !"".equals(oldInvoice.getCreatestaff())) {
            column.append(",CREATESTAFF");
            value.append(",'"+oldInvoice.getCreatestaff()+"'");
        }
        if(oldInvoice.getTinumber() != null && !"".equals(oldInvoice.getTinumber())) {
            column.append(",TINUMBER");
            value.append(",'"+oldInvoice.getTinumber()+"'");
        }
        if(oldInvoice.getOutsideid() != null && !"".equals(oldInvoice.getOutsideid())) {
            column.append(",OUTSIDEID");
            value.append(",'"+oldInvoice.getOutsideid()+"'");
        }

        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }


    public String saveInvoicemanageMenTci(TblContractInvoicesmanagemen tci) {
        StringBuffer column = new StringBuffer("INSERT INTO TBL_CONTRACT_INVOICESMANAGEMEN (INVOICEID");
        StringBuffer value = new StringBuffer(" VALUES (CIRCULATION_SEQUENCE.nextval");

//        if(tci.getBudgetId()!= null && !"".equals(tci.getBudgetId())) {
//            column.append(",BUDGETID");
//            value.append(",'"+tci.getBudgetId()+"'");
//        }
        if(tci.getInvoiceno()!= null && !"".equals(tci.getInvoiceno())) {
            column.append(",INVOICENO");
            value.append(",'"+tci.getInvoiceno()+"'");
        }
        if(tci.getInvoicemoney() != null && !"".equals(tci.getInvoicemoney())) {
            column.append(",INVOICEMONEY");
            value.append(",'"+tci.getInvoicemoney()+"'");
        }
        if(tci.getInvoicedate() != null && !"".equals(tci.getInvoicedate())) {
            column.append(",INVOICEDATE");
            value.append(",TO_DATE('"+ DateUtil.parseDate(tci.getInvoicedate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");
        }
        if(tci.getInvoiceheadtext() != null && !"".equals(tci.getInvoiceheadtext())) {
            column.append(",INVOICEHEADTEXT");
            value.append(",'"+tci.getInvoiceheadtext()+"'");
        }
        if(tci.getInvoicepost() != null && !"".equals(tci.getInvoicepost())) {
            column.append(",INVOICEPOST");
            value.append(",'"+tci.getInvoicepost()+"'");
        }
        if(tci.getInvoicecontent() != null && !"".equals(tci.getInvoicecontent())) {
            column.append(",INVOICECONTENT");
            value.append(",'"+tci.getInvoicecontent()+"'");
        }
        if(tci.getInvoicetype() != null && !"".equals(tci.getInvoicetype())) {
            column.append(",INVOICETYPE");
            value.append(",'"+tci.getInvoicetype()+"'");
        }
        if(tci.getInvoicestatus() != null && !"".equals(tci.getInvoicestatus())) {
            column.append(",INVOICESTATUS");
            value.append(",'"+tci.getInvoicestatus()+"'");
        }
        if(tci.getInvoicespdate() != null && !"".equals(tci.getInvoicespdate())) {
            column.append(",INVOICESPDATE");
            value.append(",TO_DATE('"+ DateUtil.parseDate(tci.getInvoicespdate(),"yyyy-MM-dd HH:mm:ss") +"', 'YYYY-MM-DD HH24:MI:SS')");        }
        if(tci.getInvoicesporg() != null && !"".equals(tci.getInvoicesporg())) {
            column.append(",INVOICESPORG");
            value.append(",'"+tci.getInvoicesporg()+"'");
        }
        if(tci.getInvoicekporg() != null && !"".equals(tci.getInvoicekporg())) {
            column.append(",INVOICEKPORG");
            value.append(",'"+tci.getInvoicekporg()+"'");
        }
        if(tci.getInvoiceogr() != null && !"".equals(tci.getInvoiceogr())) {
            column.append(",INVOICEOGR");
            value.append(",'"+tci.getInvoiceogr()+"'");
        }
        if(tci.getCreatestaff() != null && !"".equals(tci.getCreatestaff())) {
            column.append(",CREATESTAFF");
            value.append(",'"+tci.getCreatestaff()+"'");
        }
        if(tci.getTinumber() != null && !"".equals(tci.getTinumber())) {
            column.append(",TINUMBER");
            value.append(",'"+tci.getTinumber()+"'");
        }
        if(tci.getOutsideid() != null && !"".equals(tci.getOutsideid())) {
            column.append(",OUTSIDEID");
            value.append(",'"+tci.getOutsideid()+"'");
        }

        column.append(")");
        value.append(")");
        String sql = column.toString()+value.toString();
        return sql;
    }
}
